package com.huaxia.dao.performance;

import com.huaxia.pojo.performance.BranchLongPerformance;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/04/26 14:36
 */

public interface BranchLongPerformanceMapper {



    @Select("select sb.branchsname,nvl(a.dayAdvance,0)branchDayAdvance,nvl(b.monthAdvance,0)branchMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))branchDayInSure,\n" +
            "(nvl(e.monthInSure,0)-nvl(f.monthYt,0))branchMonthInSure from SD_BRANCH sb left join(\n" +
            "                                select BRANCHCODE, SUM(PREPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')= \n" +
            "\t\t\tto_char(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD') and (APPLY_STATUS_CODE NOT IN\n" +
            "\t\t\t('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                               )a on sb.branchcode=a.branchcode left join(\n" +
            "                                select BRANCHCODE, SUM(PREPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=\n" +
            "\t\t\tto_char(SYSDATE,'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N') or (TO_CHAR(RECEIVE_DATE,'YYYY/MM')=to_char(add_months(trunc(sysdate),1),'YYYY/MM'))) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y' \n" +
            "\t\t\tGROUP BY BRANCHCODE )b on sb.branchcode=b.branchcode left join(\n" +
            "                                select BRANCHCODE, SUM(WRITTENPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')= \n" +
            "\t\t\tto_char(add_months(trunc(sysdate),1),'YYYY/MM') and TO_CHAR(scan_DATE,'YYYY/MM/dd')=TO_CHAR(SYSDATE,'YYYY/MM/DD') and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                                )c on sb.branchcode=c.branchcode left join(\n" +
            "                                select BRANCHCODE, SUM(WRITTENPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(APPLY_YT_DATE,'YYYY/MM/DD')\n" +
            "\t\t\t=TO_CHAR(SYSDATE,'YYYY/MM/DD') and  TO_CHAR(ISSUE_DATE,'YYYY/MM')= to_char(add_months(trunc(sysdate),1),'YYYY/MM') and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                                )d on sb.branchcode=d.branchcode left join(\n" +
            "                                select BRANCHCODE, SUM(WRITTENPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "\t\t\t=to_char(add_months(trunc(sysdate),1),'YYYY/MM') and longproduct_flag = 'Y' GROUP BY BRANCHCODE)e on sb.branchcode=e.branchcode left join(\n" +
            "                                select BRANCHCODE, SUM(WRITTENPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "\t\t\t=to_char(add_months(trunc(sysdate),1),'YYYY/MM') and TO_CHAR(ISSUE_DATE,'YYYY/MM')= to_char(add_months(trunc(sysdate),1),'YYYY/MM') and longproduct_flag = 'Y'\n" +
            "\t\t\tGROUP BY BRANCHCODE)f on sb.branchcode=f.branchcode")
    List<BranchLongPerformance> getBranchLongPerformanceNextMon();


    @Select("select sb.branchsname,nvl(a.dayAdvance,0)branchDayAdvance,nvl(b.monthAdvance,0)branchMonthAdvance,(nvl(c.dayInSure,0)-nvl(d.dayYt,0))branchDayInSure,\n" +
            "            (nvl(e.monthInSure,0)-nvl(f.monthYt,0))branchMonthInSure from SD_BRANCH sb left join(\n" +
            "                         select BRANCHCODE, SUM(PREPREM)dayAdvance from FACT_YX_PREPREM_LIST_day where TO_CHAR(RECEIVE_DATE,'YYYY/MM/DD')= \n" +
            "            TO_CHAR(SYSDATE,'YYYY/MM/DD') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)  and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                                            )a on sb.branchcode=a.branchcode left join(\n" +
            "                           select BRANCHCODE, SUM(PREPREM)monthAdvance from FACT_YX_PREPREM_LIST_day where ((TO_CHAR(RECEIVE_DATE,'YYYY/MM')=TO_CHAR(SYSDATE,'YYYY/MM') and (IS_IMMEDIATE_TRANSFER = 'Y' or IS_IMMEDIATE_TRANSFER is null)) or\n" +
            "(TO_CHAR(RECEIVE_DATE,'YYYY/MM') = TO_char(add_months(trunc(sysdate),-1),'YYYY/MM') and IS_IMMEDIATE_TRANSFER = 'N')) and (APPLY_STATUS_CODE NOT IN('2040','4010','10') or APPLY_STATUS_CODE IS NULL) and longproduct_flag = 'Y'\n" +
            "            GROUP BY BRANCHCODE \n" +
            "\t\t           )b on sb.branchcode=b.branchcode left join(\n" +
            "                          select BRANCHCODE, SUM(WRITTENPREM)dayInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM/DD')= \n" +
            "            TO_CHAR(SYSDATE,'YYYY/MM/DD') and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                                            )c on sb.branchcode=c.branchcode left join(\n" +
            "                          select BRANCHCODE, SUM(WRITTENPREM)dayYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM/DD')\n" +
            "             = TO_CHAR(SYSDATE,'YYYY/MM/DD') and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "                                            )d on sb.branchcode=d.branchcode left join(\n" +
            "                          select BRANCHCODE, SUM(WRITTENPREM)monthInSure from FACT_YX_PREPREM_LIST_day where TO_CHAR(ISSUE_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM') and longproduct_flag = 'Y'  GROUP BY BRANCHCODE\n" +
            "\t\t          )e on sb.branchcode=e.branchcode left join(\n" +
            "                         select BRANCHCODE, SUM(WRITTENPREM)monthYt from FACT_YX_PREPREM_LIST_day where TO_CHAR(YT_DATE,'YYYY/MM')\n" +
            "            =TO_CHAR(SYSDATE,'YYYY/MM')  and longproduct_flag = 'Y' GROUP BY BRANCHCODE\n" +
            "\t                        )f on sb.branchcode=f.branchcode")
    List<BranchLongPerformance> getBranchLongPerformanceThisMon();





    //当月目标
    @Select("select t.BRANCHSNAME,t.GOAL from SD_YX_TARGET t \n" +
            "where t.utime=(\n" +
            "select u.utime from(\n" +
            "select t.utime from SD_YX_TARGET t where extract(month from sysdate)=t.umonth and\n" +
            "to_char(to_date(t.utime,'yyyy/mm/dd'),'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.utime>=to_char(sysdate,'yyyy/mm/dd') order by t.utime\n" +
            ")u where rownum=1\n" +
            ")")
    List<Map> findYxTargetThisMon();


    //跨月目标
    @Select("select t.BRANCHSNAME,t.GOAL from SD_YX_TARGET t \n" +
            "where t.utime=(\n" +
            "select u.utime from(\n" +
            "select t.utime from SD_YX_TARGET t where extract(month from add_months(trunc(sysdate),1))=t.umonth and\n" +
            "t.utime>=to_char(sysdate,'yyyy/mm/dd') order by t.utime\n" +
            ")u where rownum=1\n" +
            ")")
    List<Map> findYxTargetNextMon();



}
 